

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" href="/img/favicon.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="Blue~u~u~u">
  <meta name="author" content="Blue~u~u~u">
  <meta name="keywords" content="">
  <meta name="description" content="MyCat入门配置详解和常见九种数据分片算法第一节 分库分表概述1、为什么要拆分？①MySQL 实例内部结构[1]单一架构 [2]复制架构尽管搭建了复制架构，但是实际上从逻辑上来说仍然只有一个 db_hr 数据库。  ②性能瓶颈MySQL 工作过程中的性能瓶颈主要来自于下面三个方面（同等硬件条件下）：  数据存储量：单表 1000 万条数据达到极限；500 万条开始性能明显下降；300 万条开始就">
<meta property="og:type" content="article">
<meta property="og:title" content="MyCat入门配置详解和常见九种数据分片算法">
<meta property="og:url" content="http://www.slx.blue/2022/03/15/MyCat/index.html">
<meta property="og:site_name" content="Blue~u~u~u~u">
<meta property="og:description" content="MyCat入门配置详解和常见九种数据分片算法第一节 分库分表概述1、为什么要拆分？①MySQL 实例内部结构[1]单一架构 [2]复制架构尽管搭建了复制架构，但是实际上从逻辑上来说仍然只有一个 db_hr 数据库。  ②性能瓶颈MySQL 工作过程中的性能瓶颈主要来自于下面三个方面（同等硬件条件下）：  数据存储量：单表 1000 万条数据达到极限；500 万条开始性能明显下降；300 万条开始就">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat01.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat02.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat03.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat04.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat05.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat06.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat07.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat08.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat09.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat10.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat11.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat12.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat13.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat14.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat15.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat16.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat17.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat18.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat19.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat20.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat21.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat22.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat23%E3%80%81.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat24.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat25.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat26.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat27.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat28.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat29.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat30.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat31.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat32.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat33.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat34.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat35.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat36.png">
<meta property="article:published_time" content="2022-03-15T10:21:20.000Z">
<meta property="article:modified_time" content="2022-03-15T10:21:59.885Z">
<meta property="article:author" content="Blue~u~u~u">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat01.png">
  
  <title>MyCat入门配置详解和常见九种数据分片算法 - Blue~u~u~u~u</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@10/styles/github-gist.min.css" />
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.css" />
  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_kmeydafke9r.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"www.slx.blue","root":"/","version":"1.8.12","typing":{"enable":true,"typeSpeed":180,"cursorChar":"_","loop":true},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"right","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname"}},"search_path":"/local-search.xml"};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.4.0"></head>

<script src="https://cdn.jsdelivr.net/npm/jquery/dist/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/font-awesome/css/font-awesome.min.css"/>
<script src="/live2d-widget/autoload.js"></script>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>Blue~u~u</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/links/">
                <i class="iconfont icon-link-fill"></i>
                友链
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('/images/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="MyCat入门配置详解和常见九种数据分片算法">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2022-03-15 18:21" pubdate>
        2022年3月15日 晚上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      21k 字
    </span>
  

  
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      64 分钟
    </span>
  

  
  
    
      <!-- 不蒜子统计文章PV -->
      <span id="busuanzi_container_page_pv" style="display: none">
        <i class="iconfont icon-eye" aria-hidden="true"></i>
        <span id="busuanzi_value_page_pv"></span> 次
      </span>
    
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">MyCat入门配置详解和常见九种数据分片算法</h1>
            
              <p class="note note-info">
                
                  本文最后更新于：2 个月前
                
              </p>
            
            <div class="markdown-body">
              <h1 id="MyCat入门配置详解和常见九种数据分片算法"><a href="#MyCat入门配置详解和常见九种数据分片算法" class="headerlink" title="MyCat入门配置详解和常见九种数据分片算法"></a>MyCat入门配置详解和常见九种数据分片算法</h1><h1 id="第一节-分库分表概述"><a href="#第一节-分库分表概述" class="headerlink" title="第一节 分库分表概述"></a>第一节 分库分表概述</h1><h2 id="1、为什么要拆分？"><a href="#1、为什么要拆分？" class="headerlink" title="1、为什么要拆分？"></a>1、为什么要拆分？</h2><h3 id="①MySQL-实例内部结构"><a href="#①MySQL-实例内部结构" class="headerlink" title="①MySQL 实例内部结构"></a>①MySQL 实例内部结构</h3><h4 id="1-单一架构"><a href="#1-单一架构" class="headerlink" title="[1]单一架构"></a>[1]单一架构</h4><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat01.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="2-复制架构"><a href="#2-复制架构" class="headerlink" title="[2]复制架构"></a>[2]复制架构</h4><p>尽管搭建了复制架构，但是实际上从逻辑上来说仍然只有一个 db_hr 数据库。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat02.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="②性能瓶颈"><a href="#②性能瓶颈" class="headerlink" title="②性能瓶颈"></a>②性能瓶颈</h3><p>MySQL 工作过程中的性能瓶颈主要来自于下面三个方面（同等硬件条件下）：</p>
<ul>
<li>数据存储量：单表 1000 万条数据达到极限；500 万条开始性能明显下降；300 万条开始就应该考虑拆分。</li>
<li>I/O 瓶颈：关系型数据库以硬盘作为主要存储介质，所以必然存在 I/O 瓶颈。</li>
<li>访问量瓶颈：通常 MySQL 的最大连接数默认是100，最大可以达到 16384。</li>
</ul>
<p>由此我们可以看出，对数据库进行拆分主要是出于数据量不断增加的挑战。</p>
<h2 id="2、拆分方式"><a href="#2、拆分方式" class="headerlink" title="2、拆分方式"></a>2、拆分方式</h2><h3 id="①垂直拆分"><a href="#①垂直拆分" class="headerlink" title="①垂直拆分"></a>①垂直拆分</h3><p>垂直拆分是最容易想到的拆分方式。它按照项目的业务功能模块，把从属于不同模块的数据库表分散到对应的不同数据库中。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat03.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>这种拆分方式的优缺点是：</p>
<ul>
<li>优点<ul>
<li>拆分规则明确，按照不同的功能模块或服务分配不同数据库</li>
<li>数据维护与定位简单</li>
</ul>
</li>
<li>缺点<ul>
<li>并没有解决单表数据量太大的问题</li>
<li>会出现跨库 join</li>
<li>需要对上层应用系统的代码进行重构，修改原有的事务操作</li>
</ul>
</li>
</ul>
<h3 id="②水平拆分"><a href="#②水平拆分" class="headerlink" title="②水平拆分"></a>②水平拆分</h3><p>针对一张数据量很大的表，把它拆分为多张表，数据分流保存到各个拆分后的数据库表中。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat04.png" srcset="/img/loading.gif" lazyload></p>
<p>如果数据量继续增加，超过一个单库能够容纳的极限则需要继续分库：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat05.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>这种拆分方式的优缺点评价：</p>
<ul>
<li>只分表不分库：<ul>
<li>同库无分布式事务问题，事务处理相对简单</li>
<li>同库无跨库 join 问题</li>
<li>表拆分后不存在超大型表的性能问题</li>
<li>只要拆分规则定义好，很难出现扩展性的限制，但拆分规则设定并不简单，规则一定会和业务挂钩，如根据 id、根据时间等。</li>
</ul>
</li>
<li>既分表又分库：<ul>
<li>异库存在分布式事务问题</li>
<li>异库存在跨库 join 问题</li>
<li>多数据源管理难度加大，代码复杂度增加</li>
</ul>
</li>
</ul>
<h2 id="3、MyCat-简介"><a href="#3、MyCat-简介" class="headerlink" title="3、MyCat 简介"></a>3、MyCat 简介</h2><p>尽管拆分后必然要面对很多问题，但是随着数据量的增加又不得不拆分。所以我们开发的上层应用系统必须有能力对接拆分后的多个数据库。MyCat 就是帮助我们实现这一功能的数据库中间件。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat06.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>MyCat 是一款数据库中间件。</p>
<p>对于应用程序来说完全透明：不管底层的数据如何拆分，应用只需要连接 MyCat 即可完成对数据的操作。</p>
<p>支持 MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流数据库。</p>
<p>MyCat <strong>不存储数据</strong>，它只是<strong>数据的路由</strong>。</p>
<p>底层拦截用户发送过来的 SQL 语句并进行分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。</p>
<p>Mycat的特性如下:</p>
<ul>
<li>支持SQL92标准</li>
<li>遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理</li>
<li>基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群</li>
<li>支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster</li>
<li>基于Nio实现，有效管理线程，高并发问题</li>
<li>支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数</li>
<li>支持单库内部任意join，支持跨库2表join</li>
<li>支持通过全局表，ER关系的分片策略，实现了高效的多表join查询</li>
<li>支持多租户方案</li>
<li>支持分布式事务</li>
<li>支持全局序列号，解决分布式下的主键生成问题</li>
<li>分片规则丰富，插件化开发，易于扩展</li>
<li>强大的web，命令行监控</li>
<li>支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb</li>
<li>支持密码加密</li>
<li>支持服务降级</li>
<li>支持IP白名单</li>
<li>支持SQL黑名单、sql注入攻击拦截</li>
<li>支持分表(1.6以后版本)</li>
<li>集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0以后版本）</li>
</ul>
<h1 id="第二节-安装与配置"><a href="#第二节-安装与配置" class="headerlink" title="第二节 安装与配置"></a>第二节 安装与配置</h1><h2 id="1、获取-MyCat-程序"><a href="#1、获取-MyCat-程序" class="headerlink" title="1、获取 MyCat 程序"></a>1、获取 MyCat 程序</h2><h3 id="①方式一"><a href="#①方式一" class="headerlink" title="①方式一"></a>①方式一</h3><p>访问这个地址下载：<a target="_blank" rel="noopener" href="https://codeload.github.com/MyCATApache/Mycat-Server/zip/Mycat-server-1675-release">https://codeload.github.com/MyCATApache/Mycat-Server/zip/Mycat-server-1675-release</a></p>
<h3 id="②方式二"><a href="#②方式二" class="headerlink" title="②方式二"></a>②方式二</h3><p>由老师发给你。</p>
<h2 id="2、解压-MyCat-压缩包"><a href="#2、解压-MyCat-压缩包" class="headerlink" title="2、解压 MyCat 压缩包"></a>2、解压 MyCat 压缩包</h2><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat07.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="3、使用-IDEA-打开"><a href="#3、使用-IDEA-打开" class="headerlink" title="3、使用 IDEA 打开"></a>3、使用 IDEA 打开</h2><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat08.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="4、配置-schema-xml"><a href="#4、配置-schema-xml" class="headerlink" title="4、配置 schema.xml"></a>4、配置 schema.xml</h2><p>从这里开始，我们就是在初步搭建 MyCat 服务器。我们想要初步实现的目标：</p>
<ul>
<li>MyCat 中配置虚拟数据库、虚拟数据库表，物理库和物理表暂时不拆分</li>
<li>启动 MyCat</li>
<li>使用客户端连接到 MyCat</li>
</ul>
<p>而让 MyCat 连接物理库、配置虚拟库、虚拟表都需要在 schema.xml 中配置。</p>
<h3 id="①配置文件位置"><a href="#①配置文件位置" class="headerlink" title="①配置文件位置"></a>①配置文件位置</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat09.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="②告诉-MyCat-如何连接物理库"><a href="#②告诉-MyCat-如何连接物理库" class="headerlink" title="②告诉 MyCat 如何连接物理库"></a>②告诉 MyCat 如何连接物理库</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">	dataHost 标签：配置物理库的连接方式。可以配置多个。本质上是连接到数据库服务器。</span><br><span class="hljs-comment">	name 属性：给当前 dataHost 标签命名，便于其它地方引用。</span><br><span class="hljs-comment"> --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">dataHost</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;virtual-host-hello&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">maxCon</span>=<span class="hljs-string">&quot;1000&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">minCon</span>=<span class="hljs-string">&quot;10&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">balance</span>=<span class="hljs-string">&quot;0&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">writeType</span>=<span class="hljs-string">&quot;0&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">dbType</span>=<span class="hljs-string">&quot;mysql&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">dbDriver</span>=<span class="hljs-string">&quot;jdbc&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">switchType</span>=<span class="hljs-string">&quot;1&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">slaveThreshold</span>=<span class="hljs-string">&quot;100&quot;</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">		heartbeat 标签：设置做心跳检查时使用的 SQL 语句。</span><br><span class="hljs-comment">	--&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">heartbeat</span>&gt;</span>select user()<span class="hljs-tag">&lt;/<span class="hljs-name">heartbeat</span>&gt;</span><br>	<span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">		writeHost 标签：具体配置连接物理库的信息，可以有多个。</span><br><span class="hljs-comment">		host 属性：命名 一般writeHost用M1 readHost用S1。</span><br><span class="hljs-comment">		url 属性：物理库的 URL 地址</span><br><span class="hljs-comment">		user 属性：连接物理库使用的用户名</span><br><span class="hljs-comment">		password 属性：连接物理库使用的密码</span><br><span class="hljs-comment">	 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">writeHost</span> <span class="hljs-attr">host</span>=<span class="hljs-string">&quot;hostM1&quot;</span></span><br><span class="hljs-tag">			   <span class="hljs-attr">url</span>=<span class="hljs-string">&quot;jdbc:mysql://localhost:3306&quot;</span></span><br><span class="hljs-tag">			   <span class="hljs-attr">user</span>=<span class="hljs-string">&quot;root&quot;</span></span><br><span class="hljs-tag">			   <span class="hljs-attr">password</span>=<span class="hljs-string">&quot;root&quot;</span>/&gt;</span><br><br><span class="hljs-tag">&lt;/<span class="hljs-name">dataHost</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="③配置-dataNode"><a href="#③配置-dataNode" class="headerlink" title="③配置 dataNode"></a>③配置 dataNode</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">	dataNode 标签：配置数据节点。本质上是连接到数据库服务器上的一个具体物理数据库。</span><br><span class="hljs-comment">	name 属性：给当前 dataNode 标签命名，便于其它地方引用。</span><br><span class="hljs-comment">	dataHost 属性：引用一个已经配置好的 dataHost。</span><br><span class="hljs-comment">	database 属性：指定一个具体的物理库的名称。</span><br><span class="hljs-comment">--&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">dataNode</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;data-node-hello&quot;</span> <span class="hljs-attr">dataHost</span>=<span class="hljs-string">&quot;virtual-host-hello&quot;</span> <span class="hljs-attr">database</span>=<span class="hljs-string">&quot;db_hr&quot;</span> /&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="④创建虚拟库和虚拟表"><a href="#④创建虚拟库和虚拟表" class="headerlink" title="④创建虚拟库和虚拟表"></a>④创建虚拟库和虚拟表</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">	schema 标签：配置虚拟库，里面子标签配置虚拟表。</span><br><span class="hljs-comment">	name 属性：给当前 schema 标签命名，便于其它地方引用。同时也是指定虚拟数据库的名称。</span><br><span class="hljs-comment"> --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">schema</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;virtual-db-hello&quot;</span> <span class="hljs-attr">checkSQLschema</span>=<span class="hljs-string">&quot;true&quot;</span> <span class="hljs-attr">sqlMaxLimit</span>=<span class="hljs-string">&quot;100&quot;</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- table 标签：配置虚拟表。 --&gt;</span><br>	<span class="hljs-comment">&lt;!-- name 属性：指定虚拟表的名称。 --&gt;</span><br>	<span class="hljs-comment">&lt;!-- dataNode 属性：通过指定 dataNode 指定当前虚拟表要连接的物理库。如果有多个，可以使用逗号分开，也可以使用 dn$1-3 这样的表达式。 --&gt;</span><br>	<span class="hljs-comment">&lt;!-- subTables 属性：指定虚拟表对应的物理表。 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;virtual_t_emp&quot;</span></span><br><span class="hljs-tag">		   <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;emp_id&quot;</span></span><br><span class="hljs-tag">		   <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;data-node-hello&quot;</span></span><br><span class="hljs-tag">		   <span class="hljs-attr">autoIncrement</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">		   <span class="hljs-attr">fetchStoreNodeByJdbc</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">		   <span class="hljs-attr">subTables</span>=<span class="hljs-string">&quot;t_emp&quot;</span></span><br><span class="hljs-tag">	/&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">schema</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="5、配置-server-xml"><a href="#5、配置-server-xml" class="headerlink" title="5、配置 server.xml"></a>5、配置 server.xml</h2><h3 id="①配置文件位置-1"><a href="#①配置文件位置-1" class="headerlink" title="①配置文件位置"></a>①配置文件位置</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat10.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="②配置-MyCat-自身的连接信息"><a href="#②配置-MyCat-自身的连接信息" class="headerlink" title="②配置 MyCat 自身的连接信息"></a>②配置 MyCat 自身的连接信息</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- user 标签：配置外界连接 MyCat 时使用的连接信息 --&gt;</span><br><span class="hljs-comment">&lt;!-- name 属性：指定用户名。 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">user</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;myCat&quot;</span>&gt;</span><br>	<span class="hljs-comment">&lt;!-- password 属性：指定密码。 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;password&quot;</span>&gt;</span>hello<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- schemas 属性：要连接的虚拟库的名称。</span><br><span class="hljs-comment">	请到 schema.xml 配置文件中找到 schema 标签，复制过来 schema 标签的 name 属性。 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;schemas&quot;</span>&gt;</span>virtual-db-hello<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">user</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="6、启动-MyCat"><a href="#6、启动-MyCat" class="headerlink" title="6、启动 MyCat"></a>6、启动 MyCat</h2><h3 id="①找到-MyCat-启动类"><a href="#①找到-MyCat-启动类" class="headerlink" title="①找到 MyCat 启动类"></a>①找到 MyCat 启动类</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat11.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="②配置-JVM-参数"><a href="#②配置-JVM-参数" class="headerlink" title="②配置 JVM 参数"></a>②配置 JVM 参数</h3><ul>
<li>指定 MYCAT_HOME：这个参数是需要指定 MyCat 工程的 src/main 目录的完整路径。</li>
<li>指定直接内存大小：如果 512M 不够，那就指定 1024M。</li>
</ul>
<blockquote>
<p>-DMYCAT_HOME=D:\idea2019workspace\mycat210722\src\main<br>-XX:MaxDirectMemorySize=512M</p>
</blockquote>
<h3 id="③运行程序"><a href="#③运行程序" class="headerlink" title="③运行程序"></a>③运行程序</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat12.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="7、两个坑"><a href="#7、两个坑" class="headerlink" title="7、两个坑"></a>7、两个坑</h2><h3 id="①第一个"><a href="#①第一个" class="headerlink" title="①第一个"></a>①第一个</h3><p>如果 schema 标签的配置是从官方提供的配置中复制过来的，那么可能会带有 randomDataNode 属性，此时会有下面问题：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat13.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>如果尝试连接 MyCat 时，没有指定一个具体的数据库名称，MyCat 会访问 randomDataNode 属性指定的数据节点。而如果这个数据节点不存在，那么就会访问失败。</p>
<h3 id="②第二个"><a href="#②第二个" class="headerlink" title="②第二个"></a>②第二个</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat14.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>调整 JDK 版本操作如下：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat15.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat16.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h1 id="第三节-数据分片"><a href="#第三节-数据分片" class="headerlink" title="第三节 数据分片"></a>第三节 数据分片</h1><h2 id="1、数据分片概念"><a href="#1、数据分片概念" class="headerlink" title="1、数据分片概念"></a>1、数据分片概念</h2><p>从虚拟库、虚拟表看到的数据，逻辑上是一个整体。而实际上数据的物理存储是分散在不同物理库、物理表中。每个实际的物理表可以看成是一个数据分片。</p>
<p>数据进入数据库时，经过不同<strong>拆分规则</strong>的分流进入了不同的数据分片。我们对拆分规则有两点期望：</p>
<ul>
<li>数据存储的过程中：执行 insert 语句后将数据准确插入到对应分片</li>
<li>数据提取的过程中：能够准确的从指定分片查询到我们需要的数据</li>
</ul>
<h2 id="2、数据分片的具体算法"><a href="#2、数据分片的具体算法" class="headerlink" title="2、数据分片的具体算法"></a>2、数据分片的具体算法</h2><p>[取模分片](# ①取模分片)</p>
<p>[全局 id 分片](# ②全局 id 分片)</p>
<p>[枚举分片](# ③枚举分片)</p>
<p>[固定 hash 分片](# ④固定 hash 分片)</p>
<p>[固定范围分片](# ⑤固定范围分片)</p>
<p>[取模范围分片](# ⑥取模范围分片)</p>
<p>[字符串 hash 分片](# ⑦字符串 hash 分片)</p>
<p>[时间分片](# ⑧时间分片)</p>
<p>[一致性 hash 分片](# ⑨一致性 hash 分片)</p>
<h1 id="第四节-跨库-join"><a href="#第四节-跨库-join" class="headerlink" title="第四节 跨库 join"></a>第四节 跨库 join</h1><p>概念：A 表和 B 表做关联查询，但是 A 表和 B 表不在同一个数据库中。</p>
<h2 id="1、全局表"><a href="#1、全局表" class="headerlink" title="1、全局表"></a>1、全局表</h2><p>系统中基本都会存在数据字典信息，如数据分类信息、项目的配置信息等。这些字典数据最大的特点就是数据量不大并且很少会被改变。同时绝大多数的业务场景都会涉及到字典表的操作。 因此为了避免频繁的跨库join操作，结合冗余数据思想，可以考虑把这些字典信息在每一个分库中都存在一份。</p>
<p>mycat在进行join操作时，当业务表与全局表进行聚合会优先选择相同分片的全局表，从而避免跨库join操作。在进行数据插入时，会把数据同时插入到所有分片的全局表中。</p>
<p>修改 schema.xml</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;tb_global&quot;</span> <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;dn142,dn145&quot;</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;global_id&quot;</span> <span class="hljs-attr">type</span>=<span class="hljs-string">&quot;global&quot;</span>/&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="2、ER表"><a href="#2、ER表" class="headerlink" title="2、ER表"></a>2、ER表</h2><p>ER 表也是一种为了避免跨库join的手段，在业务开发时，经常会使用到主从表关系的查询，如商品表与商品详情表。</p>
<p>ER 表的出现就是为了让有关系的表数据存储于同一个分片中，从而避免跨库 join 的出现。</p>
<p>修改 schema.xml</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;tb_goods&quot;</span> <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;dn129,dn130&quot;</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;goods_id&quot;</span> <span class="hljs-attr">rule</span>=<span class="hljs-string">&quot;sharding-by-murmur-goods&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">childTable</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;tb_goods_detail&quot;</span> <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;goods_detail_id&quot;</span> <span class="hljs-attr">joinKey</span>=<span class="hljs-string">&quot;goods_id&quot;</span> <span class="hljs-attr">parentKey</span>=<span class="hljs-string">&quot;goods_id&quot;</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">childTable</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">table</span>&gt;</span><br></code></pre></td></tr></table></figure>



<p>再次添加 goods 数据的时候,有关系的表数据存储于同一个分片中</p>
<h1 id="附：分片算法"><a href="#附：分片算法" class="headerlink" title="附：分片算法"></a>附：分片算法</h1><h1 id="①取模分片"><a href="#①取模分片" class="headerlink" title="①取模分片"></a>①取模分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解"><a href="#1、理解" class="headerlink" title="1、理解"></a>1、理解</h2><p>根据 id 值进行模运算，然后根据取模的计算结果决定数据分流后存入的目标物理表。这里涉及到的用于取模计算的数据库表字段值，<strong>不能指望由数据库自增</strong>来得到——因为这个数据是在决定分流到哪个物理表时用到的，此时还没有执行 insert 语句。所以这个数据必须由程序员指定，<strong>在 Java 代码中生成</strong>。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat17.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="2、操作"><a href="#2、操作" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①创建用于测试的物理表"><a href="#①创建用于测试的物理表" class="headerlink" title="①创建用于测试的物理表"></a>①创建用于测试的物理表</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `db_hr`.`t_emp1` ( `emp_id` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, `emp_name` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), <span class="hljs-keyword">PRIMARY</span> KEY (`emp_id`) ); <br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `db_hr`.`t_emp2` ( `emp_id` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, `emp_name` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), <span class="hljs-keyword">PRIMARY</span> KEY (`emp_id`) ); <br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `db_hr`.`t_emp3` ( `emp_id` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, `emp_name` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), <span class="hljs-keyword">PRIMARY</span> KEY (`emp_id`) ); <br></code></pre></td></tr></table></figure>



<h3 id="②配置-subTables-属性"><a href="#②配置-subTables-属性" class="headerlink" title="②配置 subTables 属性"></a>②配置 subTables 属性</h3><ul>
<li>所在位置：schema.xml 配置文件中 schema 标签的子标签 table 的 subTables 属性。</li>
<li>属性值设置方式：<ul>
<li>单个值：t_user</li>
<li>多个值：<ul>
<li>将多个物理表名称用逗号隔开：t_user1,t_user2,t_user3</li>
<li>使用正则表达式格式指定数值区间：t_user$1-5</li>
<li>将多个用区间表示的物理表名称用逗号隔开：t_user$1-5,t_user$7-10</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="③配置拆分规则"><a href="#③配置拆分规则" class="headerlink" title="③配置拆分规则"></a>③配置拆分规则</h3><ul>
<li>在 schema.xml 中配置 rule 属性</li>
</ul>
<p>在 table 标签中通过 rule 属性指定规则名称即可。当前取模分片的名称是 mod-lang，这些规则名称是在 rule.xml 中定义的。</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 取模分片 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 1：通过 subTables 属性指定物理表 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 2：通过 rule 属性指定拆分规则 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;virtual_t_emp&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;emp_id&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;data-node-hello&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">autoIncrement</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">fetchStoreNodeByJdbc</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">subTables</span>=<span class="hljs-string">&quot;t_emp$1-3&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">rule</span>=<span class="hljs-string">&quot;mod-long&quot;</span></span><br><span class="hljs-tag">/&gt;</span><br></code></pre></td></tr></table></figure>

<ul>
<li><p>在 rule.xml 中配置 mod-long 规则</p>
<ul>
<li>配置 tableRule 标签</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;mod-long&quot;</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>		<span class="hljs-comment">&lt;!-- 指定用于执行取模分片的字段 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_id<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>		<br>		<span class="hljs-comment">&lt;!-- 具体规则名称 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>mod-long<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>	<span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br></code></pre></td></tr></table></figure>



<ul>
<li>配置 function 标签</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;mod-long&quot;</span> <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByMod&quot;</span>&gt;</span><br>	<span class="hljs-comment">&lt;!-- 物理表的数量，需要正好就是取模的数值 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;count&quot;</span>&gt;</span>3<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure></li>
</ul>
<h3 id="④重启-MyCat"><a href="#④重启-MyCat" class="headerlink" title="④重启 MyCat"></a>④重启 MyCat</h3><p>MyCat 配置文件修改后重启 MyCat 程序才能够生效。</p>
<h2 id="3、测试"><a href="#3、测试" class="headerlink" title="3、测试"></a>3、测试</h2><h3 id="①数据存储操作"><a href="#①数据存储操作" class="headerlink" title="①数据存储操作"></a>①数据存储操作</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;tom01&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">2</span>, <span class="hljs-string">&#x27;tom02&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">3</span>, <span class="hljs-string">&#x27;tom03&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">4</span>, <span class="hljs-string">&#x27;tom04&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">5</span>, <span class="hljs-string">&#x27;tom05&#x27;</span>);<br></code></pre></td></tr></table></figure>



<p>数据插入后，请打开物理表查看是否分流到了三个物理表。</p>
<ul>
<li><p>t_emp1</p>
<p>emp_id emp_name</p>
<p>3 tom03</p>
</li>
<li><p>t_emp2</p>
<p>emp_id emp_name</p>
<p>1 tom01<br>4 tom04</p>
</li>
<li><p>t_emp3</p>
<p>emp_id emp_name</p>
<p>2 tom02<br>5 tom05</p>
</li>
</ul>
<h3 id="②数据查询操作"><a href="#②数据查询操作" class="headerlink" title="②数据查询操作"></a>②数据查询操作</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> emp_id,emp_name <span class="hljs-keyword">FROM</span> virtual_t_emp <span class="hljs-keyword">WHERE</span> emp_id<span class="hljs-operator">=</span><span class="hljs-number">4</span>;<br></code></pre></td></tr></table></figure>

<p>效果：</p>
<p>emp_id emp_name</p>
<figure class="highlight basic"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs basic"><span class="hljs-symbol">4 </span>     tom04     <br></code></pre></td></tr></table></figure>

<h2 id="4、注意"><a href="#4、注意" class="headerlink" title="4、注意"></a>4、注意</h2><p>如果在 insert 语句中没有指定 emp_id 字段，则有下面的报错：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat18.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h1 id="②全局-id-分片"><a href="#②全局-id-分片" class="headerlink" title="②全局 id 分片"></a>②全局 id 分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-1"><a href="#1、理解-1" class="headerlink" title="1、理解"></a>1、理解</h2><p>全局 id 分片和上面的取模分片就一个区别：id 的来源不同。</p>
<ul>
<li>取模分片：由程序员提供 id 值。</li>
<li>全局 id 分片：由 MyCat 提供 id 值。</li>
</ul>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat19.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>具体来说，MyCat 提供 id 值有下面这些办法：</p>
<ul>
<li>基于本地文件</li>
<li>基于数据库</li>
<li>基于 zookeeper</li>
<li>基于时间戳</li>
</ul>
<h2 id="2、操作-1"><a href="#2、操作-1" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①基于本地文件"><a href="#①基于本地文件" class="headerlink" title="①基于本地文件"></a>①基于本地文件</h3><h4 id="第一步：配置-sequence-conf-properties"><a href="#第一步：配置-sequence-conf-properties" class="headerlink" title="第一步：配置 sequence_conf.properties"></a>第一步：配置 sequence_conf.properties</h4><p>文件位置：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat20.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>配置内容：</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs properties"><span class="hljs-comment"># 使用过的历史分段，可不配置</span><br><span class="hljs-meta">USER.HISIDS</span>=<span class="hljs-string"></span><br><span class="hljs-comment"></span><br><span class="hljs-comment"># ID 的起始值，从这个值开始生成 ID</span><br><span class="hljs-meta">USER.MINID</span>=<span class="hljs-string">1</span><br><span class="hljs-comment"></span><br><span class="hljs-comment"># 最大ID值</span><br><span class="hljs-meta">USER.MAXID</span>=<span class="hljs-string">200000</span><br><span class="hljs-comment"></span><br><span class="hljs-comment"># 当前ID值</span><br><span class="hljs-meta">USER.CURID</span>=<span class="hljs-string">1000</span><br></code></pre></td></tr></table></figure>



<p>配置中属性名前缀的作用：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat21.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第二步：配置-server-xml"><a href="#第二步：配置-server-xml" class="headerlink" title="第二步：配置 server.xml"></a>第二步：配置 server.xml</h4><p>指定全局 id 分片具体使用的 id 生成方式。</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!--设置全局序号生成方式</span><br><span class="hljs-comment">   0：文件</span><br><span class="hljs-comment">   1：数据库</span><br><span class="hljs-comment">   2：时间戳</span><br><span class="hljs-comment">   3：zookeeper</span><br><span class="hljs-comment">  --&gt;</span><br><span class="hljs-comment">&lt;!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况--&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sequenceHandlerType&quot;</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h4 id="第三步：测试"><a href="#第三步：测试" class="headerlink" title="第三步：测试"></a>第三步：测试</h4><p>别忘记重启 MyCat。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP&#x27;</span>, <span class="hljs-string">&#x27;jerry01&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP&#x27;</span>, <span class="hljs-string">&#x27;jerry02&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP&#x27;</span>, <span class="hljs-string">&#x27;jerry03&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP&#x27;</span>, <span class="hljs-string">&#x27;jerry04&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP&#x27;</span>, <span class="hljs-string">&#x27;jerry05&#x27;</span>);<br></code></pre></td></tr></table></figure>



<h3 id="②基于数据库"><a href="#②基于数据库" class="headerlink" title="②基于数据库"></a>②基于数据库</h3><h4 id="第一步：在物理库建表"><a href="#第一步：在物理库建表" class="headerlink" title="第一步：在物理库建表"></a>第一步：在物理库建表</h4><p>使用 MyCat 提供的一个 SQL 文件：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat22.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>执行的效果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat23%E3%80%81.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第二步：在-MYCAT-SEQUENCE-表中增加一条记录"><a href="#第二步：在-MYCAT-SEQUENCE-表中增加一条记录" class="headerlink" title="第二步：在 MYCAT_SEQUENCE 表中增加一条记录"></a>第二步：在 MYCAT_SEQUENCE 表中增加一条记录</h4><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat24.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第三步：配置-sequence-db-conf-properties"><a href="#第三步：配置-sequence-db-conf-properties" class="headerlink" title="第三步：配置 sequence_db_conf.properties"></a>第三步：配置 sequence_db_conf.properties</h4><p>文件位置：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat25.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>配置内容：</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs properties"><span class="hljs-attr">EMP_INCR</span>=<span class="hljs-string">data-node-hello</span><br></code></pre></td></tr></table></figure>



<p>属性名和其他配置的关系：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat26.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第四步：配置-server-xml"><a href="#第四步：配置-server-xml" class="headerlink" title="第四步：配置 server.xml"></a>第四步：配置 server.xml</h4><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 指定全局 id 分片方式 --&gt;</span><br><span class="hljs-comment">&lt;!-- 	0 表示基于文件 --&gt;</span><br><span class="hljs-comment">&lt;!-- 	1 表示基于数据库 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sequenceHandlerType&quot;</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h4 id="第五步：重启-MyCat"><a href="#第五步：重启-MyCat" class="headerlink" title="第五步：重启 MyCat"></a>第五步：重启 MyCat</h4><p>略。</p>
<h4 id="第六步：测试"><a href="#第六步：测试" class="headerlink" title="第六步：测试"></a>第六步：测试</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP_INCR&#x27;</span>, <span class="hljs-string">&#x27;kate01&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP_INCR&#x27;</span>, <span class="hljs-string">&#x27;kate02&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP_INCR&#x27;</span>, <span class="hljs-string">&#x27;kate03&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP_INCR&#x27;</span>, <span class="hljs-string">&#x27;kate04&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_EMP_INCR&#x27;</span>, <span class="hljs-string">&#x27;kate05&#x27;</span>);<br><br><span class="hljs-keyword">SELECT</span> emp_id,emp_name <span class="hljs-keyword">FROM</span> virtual_t_emp;<br></code></pre></td></tr></table></figure>



<h3 id="③基于时间戳"><a href="#③基于时间戳" class="headerlink" title="③基于时间戳"></a>③基于时间戳</h3><h4 id="第一步：配置-server-xml"><a href="#第一步：配置-server-xml" class="headerlink" title="第一步：配置 server.xml"></a>第一步：配置 server.xml</h4><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 指定全局 id 分片方式 --&gt;</span><br><span class="hljs-comment">&lt;!-- 	0 表示基于文件 --&gt;</span><br><span class="hljs-comment">&lt;!-- 	1 表示基于数据库 --&gt;</span><br><span class="hljs-comment">&lt;!-- 	2 表示基于时间戳 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sequenceHandlerType&quot;</span>&gt;</span>2<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h4 id="第二步：配置-sequence-time-conf-properties"><a href="#第二步：配置-sequence-time-conf-properties" class="headerlink" title="第二步：配置 sequence_time_conf.properties"></a>第二步：配置 sequence_time_conf.properties</h4><p>WORKID 与 DATAACENTERID 都是 0-31 任意整数。多 mycat 节点下，每个节点的 WORKID、DATAACENTERID 不能重复，组成唯一标识，总共支持 32*32=1024 种组合。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat27.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第三步：修改物理表-emp-id-字段宽度"><a href="#第三步：修改物理表-emp-id-字段宽度" class="headerlink" title="第三步：修改物理表 emp_id 字段宽度"></a>第三步：修改物理表 emp_id 字段宽度</h4><p>基于时间戳的方式将使用时间戳数值作为 emp_id 值，必须使用 bigint 类型。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat28.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="第四步：重启-MyCat"><a href="#第四步：重启-MyCat" class="headerlink" title="第四步：重启 MyCat"></a>第四步：重启 MyCat</h4><p>略。</p>
<h4 id="第五步：测试"><a href="#第五步：测试" class="headerlink" title="第五步：测试"></a>第五步：测试</h4><p>next value for MYCATSEQ_ 后面可以随便写。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_FOO&#x27;</span>, <span class="hljs-string">&#x27;BOB01&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_FOO&#x27;</span>, <span class="hljs-string">&#x27;BOB02&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_FOO&#x27;</span>, <span class="hljs-string">&#x27;BOB03&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_FOO&#x27;</span>, <span class="hljs-string">&#x27;BOB04&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> virtual_t_emp(emp_id, emp_name) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">&#x27;next value for MYCATSEQ_FOO&#x27;</span>, <span class="hljs-string">&#x27;BOB05&#x27;</span>);<br><br><span class="hljs-keyword">SELECT</span> emp_id,emp_name <span class="hljs-keyword">FROM</span> virtual_t_emp;<br></code></pre></td></tr></table></figure>



<h1 id="③枚举分片"><a href="#③枚举分片" class="headerlink" title="③枚举分片"></a>③枚举分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-2"><a href="#1、理解-2" class="headerlink" title="1、理解"></a>1、理解</h2><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat29.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="2、操作-2"><a href="#2、操作-2" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="第一步：创建数据库和表"><a href="#第一步：创建数据库和表" class="headerlink" title="第一步：创建数据库和表"></a>第一步：创建数据库和表</h3><p>在物理库所在的服务器上执行下面的 SQL 语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> DATABASE `db_hr_male`;<br><span class="hljs-keyword">CREATE</span> DATABASE `db_hr_female`; <br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `db_hr_female`.`t_emp` ( `emp_id` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, `emp_name` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), `emp_gender` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), <span class="hljs-keyword">PRIMARY</span> KEY (`emp_id`) );<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `db_hr_male`.`t_emp` ( `emp_id` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT, `emp_name` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), `emp_gender` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>), <span class="hljs-keyword">PRIMARY</span> KEY (`emp_id`) );<br></code></pre></td></tr></table></figure>



<h3 id="第二步：创建新的-dataNode"><a href="#第二步：创建新的-dataNode" class="headerlink" title="第二步：创建新的 dataNode"></a>第二步：创建新的 dataNode</h3><p>在 schema.xml 配置文件中增加配置：</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">dataNode</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;data-node-male&quot;</span> <span class="hljs-attr">dataHost</span>=<span class="hljs-string">&quot;virtual-host-hello&quot;</span> <span class="hljs-attr">database</span>=<span class="hljs-string">&quot;db_hr_male&quot;</span> /&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">dataNode</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;data-node-female&quot;</span> <span class="hljs-attr">dataHost</span>=<span class="hljs-string">&quot;virtual-host-hello&quot;</span> <span class="hljs-attr">database</span>=<span class="hljs-string">&quot;db_hr_female&quot;</span> /&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="第三步：配置-table-标签"><a href="#第三步：配置-table-标签" class="headerlink" title="第三步：配置 table 标签"></a>第三步：配置 table 标签</h3><p>在 schema.xml 配置文件中修改配置：</p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 枚举分片 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 1：将虚拟表名称设置为和物理表一样（物理表是分别放在不同物理库中的同名的表）。 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 2：让 dataNode 属性指向枚举分片涉及到的多个数据节点 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 3：去掉 subTables 属性 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 4：拆分规则改成 sharding-by-intfile --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;t_emp&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;emp_id&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;data-node-male,data-node-female&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">autoIncrement</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">fetchStoreNodeByJdbc</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">rule</span>=<span class="hljs-string">&quot;sharding-by-intfile&quot;</span></span><br><span class="hljs-tag">/&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="第四步：配置-rule-xml"><a href="#第四步：配置-rule-xml" class="headerlink" title="第四步：配置 rule.xml"></a>第四步：配置 rule.xml</h3><ul>
<li>先配置 tableRule 标签</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-intfile&quot;</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>		<span class="hljs-comment">&lt;!-- 指定提供枚举值的字段 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_gender<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>		<br>		<span class="hljs-comment">&lt;!-- 具体算法 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>hash-int<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>	<span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br></code></pre></td></tr></table></figure>



<ul>
<li>再配置 function 标签</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;hash-int&quot;</span></span><br><span class="hljs-tag">		  <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByFileMap&quot;</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- 指定另外一个配置文件，配置枚举值和数据节点之间的对应关系 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;mapFile&quot;</span>&gt;</span>partition-hash-int.txt<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!--type 属性：指定枚举值类型。默认值为0，0表示Integer，非零表示String --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;type&quot;</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!--defaultNode 当有一些特殊数据信息可以存放于默认节点中，如即不是male也不是female。默认节点：小于0表示不设置默认节点，大于等于0表示设置默认节点,不能解析的枚举就存到默认节点--&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;defaultNode&quot;</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="第五步：配置-partition-hash-int-txt"><a href="#第五步：配置-partition-hash-int-txt" class="headerlink" title="第五步：配置 partition-hash-int.txt"></a>第五步：配置 partition-hash-int.txt</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs txt">#代表第一个datanode<br>male=0<br><br>#代表第二个datanode<br>female=1<br></code></pre></td></tr></table></figure>

<h2 id="3、测试-1"><a href="#3、测试-1" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs SQL">USE virtualDB;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id, emp_name,emp_gender) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-string">&#x27;tom&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id, emp_name,emp_gender) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>, <span class="hljs-string">&#x27;kate&#x27;</span>,<span class="hljs-string">&#x27;female&#x27;</span>);<br></code></pre></td></tr></table></figure>

<h2 id="4、注意点"><a href="#4、注意点" class="headerlink" title="4、注意点"></a>4、注意点</h2><ul>
<li>table 标签中不写 subTables 属性，原因是对照枚举规则之后，不同的数据进入了不同数据库中。在各自数据库中的表名是相同的。</li>
<li>在 partition-hash-int.txt 文件中，0 和 1 这些值表示序号，这个序号对应 table 标签中 dataNode 属性值中 dataNode 名称的顺序。比如：dataNode=”dn-docker-female,dn-docker-male” 这个例子中，dn-docker-female 的序号是 0，dn-docker-male 的序号是 1。</li>
</ul>
<h1 id="④固定-hash-分片"><a href="#④固定-hash-分片" class="headerlink" title="④固定 hash 分片"></a>④固定 hash 分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-3"><a href="#1、理解-3" class="headerlink" title="1、理解"></a>1、理解</h2><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat30.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="2、操作-3"><a href="#2、操作-3" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="第一步：配置-schema-xml"><a href="#第一步：配置-schema-xml" class="headerlink" title="第一步：配置 schema.xml"></a>第一步：配置 schema.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 固定 hash 分配 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 rule 属性：partition-by-fixed-hash --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;t_emp&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;emp_id&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;data-node-male,data-node-female&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">autoIncrement</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">fetchStoreNodeByJdbc</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">rule</span>=<span class="hljs-string">&quot;partition-by-fixed-hash&quot;</span></span><br><span class="hljs-tag">/&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="第二步：配置-rule-xml"><a href="#第二步：配置-rule-xml" class="headerlink" title="第二步：配置 rule.xml"></a>第二步：配置 rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;partition-by-fixed-hash&quot;</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>		<span class="hljs-comment">&lt;!-- 指定执行 hash 运算的字段 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_id<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>		<br>		<span class="hljs-comment">&lt;!-- 具体算法 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>partition-by-fixed-hash<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>	<span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br><br><span class="hljs-comment">&lt;!--</span><br><span class="hljs-comment">  partitionCount: 各分片空间中节点的数量。格式是：m,n</span><br><span class="hljs-comment">  partitionLength: 每个分片空间中分配的范围大小。格式是：x,y</span><br><span class="hljs-comment">	计算公式是：m * x + n * y = 1024</span><br><span class="hljs-comment">	当前配置：1 * 256 + 1 * 768 = 1024</span><br><span class="hljs-comment"> --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;partition-by-fixed-hash&quot;</span> <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByLong&quot;</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;partitionCount&quot;</span>&gt;</span>1,1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;partitionLength&quot;</span>&gt;</span>256,768<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="3、测试-2"><a href="#3、测试-2" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">50</span>,<span class="hljs-string">&#x27;harry07&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">80</span>,<span class="hljs-string">&#x27;harry08&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">120</span>,<span class="hljs-string">&#x27;harry09&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">360</span>,<span class="hljs-string">&#x27;rose05&#x27;</span>,<span class="hljs-string">&#x27;female&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">1780</span>,<span class="hljs-string">&#x27;rose06&#x27;</span>,<span class="hljs-string">&#x27;female&#x27;</span>);<br></code></pre></td></tr></table></figure>



<h1 id="⑤固定范围分片"><a href="#⑤固定范围分片" class="headerlink" title="⑤固定范围分片"></a>⑤固定范围分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-4"><a href="#1、理解-4" class="headerlink" title="1、理解"></a>1、理解</h2><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat31.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="2、操作-4"><a href="#2、操作-4" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①配置-schema-xml"><a href="#①配置-schema-xml" class="headerlink" title="①配置 schema.xml"></a>①配置 schema.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 固定范围分片 --&gt;</span><br><span class="hljs-comment">&lt;!-- 配置 rule 属性：auto-sharding-long --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">table</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;t_emp&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">primaryKey</span>=<span class="hljs-string">&quot;emp_id&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">dataNode</span>=<span class="hljs-string">&quot;data-node-male,data-node-female&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">autoIncrement</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">fetchStoreNodeByJdbc</span>=<span class="hljs-string">&quot;true&quot;</span></span><br><span class="hljs-tag">	   <span class="hljs-attr">rule</span>=<span class="hljs-string">&quot;auto-sharding-long&quot;</span></span><br><span class="hljs-tag">/&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="②配置-rule-xml"><a href="#②配置-rule-xml" class="headerlink" title="②配置 rule.xml"></a>②配置 rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;auto-sharding-long&quot;</span>&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>		<span class="hljs-comment">&lt;!-- 指定用于做 hash 运算的字段 --&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_id<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>		<span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>rang-long<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>	<span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h3 id="③配置-autopartition-long-txt"><a href="#③配置-autopartition-long-txt" class="headerlink" title="③配置 autopartition-long.txt"></a>③配置 autopartition-long.txt</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat32.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs txt"># range start-end ,data node index<br># K=1000,M=10000.<br># 0-500M=0<br># 500M-1000M=1<br># 1000M-1500M=2<br><br>0-20=0<br>21-50=1<br></code></pre></td></tr></table></figure>



<h2 id="3、测试-3"><a href="#3、测试-3" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">2</span>,<span class="hljs-string">&#x27;jack13&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">4</span>,<span class="hljs-string">&#x27;jack14&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">6</span>,<span class="hljs-string">&#x27;jack15&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">35</span>,<span class="hljs-string">&#x27;rose09&#x27;</span>,<span class="hljs-string">&#x27;female&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">44</span>,<span class="hljs-string">&#x27;rose10&#x27;</span>,<span class="hljs-string">&#x27;female&#x27;</span>);<br><br><span class="hljs-keyword">SELECT</span> emp_id,emp_name,emp_gender <span class="hljs-keyword">FROM</span> t_emp;<br></code></pre></td></tr></table></figure>



<h1 id="⑥取模范围分片"><a href="#⑥取模范围分片" class="headerlink" title="⑥取模范围分片"></a>⑥取模范围分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-5"><a href="#1、理解-5" class="headerlink" title="1、理解"></a>1、理解</h2><p>取模后，根据范围决定数据节点。</p>
<h2 id="2、操作-5"><a href="#2、操作-5" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①配置-schema-xml-1"><a href="#①配置-schema-xml-1" class="headerlink" title="①配置 schema.xml"></a>①配置 schema.xml</h3><p>指定分片规则：sharding-by-partition</p>
<h3 id="②配置-rule-xml-1"><a href="#②配置-rule-xml-1" class="headerlink" title="②配置 rule.xml"></a>②配置 rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-partition&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>        <span class="hljs-comment">&lt;!-- 指定用于取模的字段 --&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_id<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>sharding-by-partition<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-partition&quot;</span> <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByPattern&quot;</span>&gt;</span><br>    <span class="hljs-comment">&lt;!-- 求模基数 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;patternValue&quot;</span>&gt;</span>256<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 默认节点 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;defaultNode&quot;</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 指定规则配置文件 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;mapFile&quot;</span>&gt;</span>partition-pattern.txt<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>

<h3 id="③配置-partition-pattern-txt"><a href="#③配置-partition-pattern-txt" class="headerlink" title="③配置 partition-pattern.txt"></a>③配置 partition-pattern.txt</h3><p>这个文件没有，需要我们自己建：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs txt">#0-128表示id%256后的数据范围。<br>0-128=0<br>129-256=1<br></code></pre></td></tr></table></figure>

<h2 id="3、测试-4"><a href="#3、测试-4" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">90</span>,<span class="hljs-string">&#x27;peter01&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">150</span>,<span class="hljs-string">&#x27;peter02&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br></code></pre></td></tr></table></figure>



<h1 id="⑦字符串-hash-分片"><a href="#⑦字符串-hash-分片" class="headerlink" title="⑦字符串 hash 分片"></a>⑦字符串 hash 分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-6"><a href="#1、理解-6" class="headerlink" title="1、理解"></a>1、理解</h2><p>在业务场景下，有时可能会根据某个分片字段的前几个值来进行取模。如地址信息只取省份、姓名只取前一个字的姓等。此时则可以使用该种方式。</p>
<p>其工作方式与取模范围分片类似，该分片方式支持数值、符号、字母取模。</p>
<p>执行流程大致是：字符串截取→hash操作→取模→根据指定范围选择 DataNode</p>
<h2 id="2、操作-6"><a href="#2、操作-6" class="headerlink" title="2、操作"></a>2、操作</h2><h4 id="1-配置-schema-xml"><a href="#1-配置-schema-xml" class="headerlink" title="[1]配置 schema.xml"></a>[1]配置 schema.xml</h4><p>指定分片规则：sharding-by-string-hash</p>
<h4 id="2-配置-rule-xml"><a href="#2-配置-rule-xml" class="headerlink" title="[2]配置 rule.xml"></a>[2]配置 rule.xml</h4><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-string-hash&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_name<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>sharding-by-string-hash-function<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-string-hash-function&quot;</span> <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByPrefixPattern&quot;</span>&gt;</span><br>    <span class="hljs-comment">&lt;!-- 求模基数 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;patternValue&quot;</span>&gt;</span>256<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 截取的位数 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;prefixLength&quot;</span>&gt;</span>1<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 细节配置文件 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;mapFile&quot;</span>&gt;</span>partition-pattern-string-hash.txt<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h4 id="3-配置-partition-pattern-string-hash-txt"><a href="#3-配置-partition-pattern-string-hash-txt" class="headerlink" title="[3]配置 partition-pattern-string-hash.txt"></a>[3]配置 partition-pattern-string-hash.txt</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs txt">0-128=0<br>129-256=1<br></code></pre></td></tr></table></figure>



<h2 id="3、测试-5"><a href="#3、测试-5" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">88</span>,<span class="hljs-string">&#x27;ccccccc&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">99</span>,<span class="hljs-string">&#x27;uvwxyz&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br></code></pre></td></tr></table></figure>



<h1 id="⑧时间分片"><a href="#⑧时间分片" class="headerlink" title="⑧时间分片"></a>⑧时间分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-7"><a href="#1、理解-7" class="headerlink" title="1、理解"></a>1、理解</h2><p>根据时间、日期信息分片。</p>
<h2 id="2、操作-7"><a href="#2、操作-7" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①修改数据库表"><a href="#①修改数据库表" class="headerlink" title="①修改数据库表"></a>①修改数据库表</h3><p>给 t_emp 表增加 emp_birthday 字段。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> `db_hr_male`.`t_emp` <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">COLUMN</span> `emp_birthday` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span> AFTER `emp_gender`;<br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> `db_hr_female`.`t_emp` <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">COLUMN</span> `emp_birthday` <span class="hljs-type">CHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span> AFTER `emp_gender`;<br></code></pre></td></tr></table></figure>

<h3 id="②配置-schema-xml"><a href="#②配置-schema-xml" class="headerlink" title="②配置 schema.xml"></a>②配置 schema.xml</h3><p>指定拆分规则：sharding-by-date</p>
<h3 id="③配置-rule-xml"><a href="#③配置-rule-xml" class="headerlink" title="③配置 rule.xml"></a>③配置 rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-date&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>        <span class="hljs-comment">&lt;!-- 指定用于分片的字段 --&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_birthday<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>partbyday<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;partbyday&quot;</span></span><br><span class="hljs-tag">          <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByDate&quot;</span>&gt;</span><br><br>    <span class="hljs-comment">&lt;!-- 日期格式 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;dateFormat&quot;</span>&gt;</span>yyyy-MM-dd<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>    <span class="hljs-comment">&lt;!-- 支持自然日分区属性 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sNaturalDay&quot;</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- 开始日期 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sBeginDate&quot;</span>&gt;</span>2014-06-01<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- 结束日期 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sEndDate&quot;</span>&gt;</span>2014-06-30<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><br>	<span class="hljs-comment">&lt;!-- 每隔几天算一个分片 --&gt;</span><br>	<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sPartionDay&quot;</span>&gt;</span>15<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="3、测试-6"><a href="#3、测试-6" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_birthday) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">666</span>, <span class="hljs-string">&#x27;2014-06-10&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_birthday) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">777</span>, <span class="hljs-string">&#x27;2014-06-25&#x27;</span>);<br></code></pre></td></tr></table></figure>

<h1 id="⑨一致性-hash-分片"><a href="#⑨一致性-hash-分片" class="headerlink" title="⑨一致性 hash 分片"></a>⑨一致性 hash 分片</h1><p>[点击返回](# 2、数据分片的具体算法)</p>
<h2 id="1、理解-8"><a href="#1、理解-8" class="headerlink" title="1、理解"></a>1、理解</h2><h3 id="①目的"><a href="#①目的" class="headerlink" title="①目的"></a>①目的</h3><p>最大限度的让数据均匀分布</p>
<h3 id="②原理"><a href="#②原理" class="headerlink" title="②原理"></a>②原理</h3><p>一致性 hash 算法引入了 hash 环的概念。环的大小是 0~2^32-1。首先通过 crc16 算法计算出数据节点在 hash 环中的位置。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat33.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>当存储数据时，也会采用同样的算法，计算出数据key的hash值，映射到hash环上。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat34.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>然后从数据映射的位置开始，以顺时针的方式找出距离最近的数据节点，接着将数据存入到该节点中。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat35.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>此时可以发现，数据并没有达到预期的数据均匀，可以发现如果两个数据节点在环上的距离，决定有大量数据存入了dataNode2，而仅有少量数据存入dataNode1。</p>
<p>为了解决数据不均匀的问题，在mycat中可以设置<strong>虚拟数据映射节点</strong>。同时这些虚拟节点会映射到实际数据节点。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mycat/MyCat36.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>数据仍然以顺时针方式寻找数据节点，当找到最近的数据节点无论是实际还是虚拟，都会进行存储，如果是虚拟数据节点的话，最终会将数据保存到实际数据节点中。 从而尽量的使数据均匀分布。</p>
<h2 id="2、操作-8"><a href="#2、操作-8" class="headerlink" title="2、操作"></a>2、操作</h2><h3 id="①配置-schema-xml-2"><a href="#①配置-schema-xml-2" class="headerlink" title="①配置 schema.xml"></a>①配置 schema.xml</h3><p>指定拆分规则：sharding-by-murmur</p>
<h3 id="②配置-rule-xml-2"><a href="#②配置-rule-xml-2" class="headerlink" title="②配置 rule.xml"></a>②配置 rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">tableRule</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;sharding-by-murmur&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">rule</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">columns</span>&gt;</span>emp_id<span class="hljs-tag">&lt;/<span class="hljs-name">columns</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">algorithm</span>&gt;</span>murmur<span class="hljs-tag">&lt;/<span class="hljs-name">algorithm</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">rule</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">tableRule</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">function</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;murmur&quot;</span></span><br><span class="hljs-tag">          <span class="hljs-attr">class</span>=<span class="hljs-string">&quot;io.mycat.route.function.PartitionByMurmurHash&quot;</span>&gt;</span><br>	<span class="hljs-comment">&lt;!-- 默认是0即可 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;seed&quot;</span>&gt;</span>0<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 要分片的数据库节点数量，必须指定，否则没法分片 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;count&quot;</span>&gt;</span>2<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br>    <br>    <span class="hljs-comment">&lt;!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 --&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">&quot;virtualBucketTimes&quot;</span>&gt;</span>160<span class="hljs-tag">&lt;/<span class="hljs-name">property</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">function</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="3、测试-7"><a href="#3、测试-7" class="headerlink" title="3、测试"></a>3、测试</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">USE virtual<span class="hljs-operator">-</span>db<span class="hljs-operator">-</span>hello;<br><br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">111</span>,<span class="hljs-string">&#x27;eeeeeeeeeee&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(emp_id,emp_name,emp_gender) <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">888</span>,<span class="hljs-string">&#x27;ttttttttttt&#x27;</span>,<span class="hljs-string">&#x27;male&#x27;</span>);<br></code></pre></td></tr></table></figure>
            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/mysql/">mysql</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/mysql/">mysql</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">
                  
                    本博客目前大部分文章都是参考尚硅谷或者马士兵教育的学习资料！<a href="http://www.atguigu.com/" rel="nofollow noopener"官网地址！</a> 
                  
                </p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2022/03/19/docker-fastdfs/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">Docker安装FastDfs和上传图片入门小程序</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2022/03/14/MySQL-%E5%A4%8D%E5%88%B6%E6%9E%B6%E6%9E%84/">
                        <span class="hidden-mobile">MySQL-复制架构</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
              <!-- Comments -->
              <article class="comments" id="comments" lazyload>
                
                  
                
                

              </article>
            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">

  <div class="footer-content">
     <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
	<!--《添加网站运行时间 -->
<br/>

<span id="timeDate">载入天数...</span><span id="times">载入时分秒...</span>
<script>
var now = new Date(); 

function createtime() {
    //此处修改你的建站时间或者网站上线时间
    var grt = new Date('11/02/2021 21:39:00');
    now.setTime(now.getTime() + 250);
    days = (now - grt) / 1000 / 60 / 60 / 24;

    dnum = Math.floor(days);
    hours = (now - grt) / 1000 / 60 / 60 - (24 * dnum);
    hnum = Math.floor(hours);
    if (String(hnum).length == 1) {
        hnum = "0" + hnum;
    }
    minutes = (now - grt) / 1000 / 60 - (24 * 60 * dnum) - (60 * hnum);
    mnum = Math.floor(minutes);
    if (String(mnum).length == 1) {
        mnum = "0" + mnum;
    }
    seconds = (now - grt) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum);
    snum = Math.round(seconds);
    if (String(snum).length == 1) {
        snum = "0" + snum;
    }
    document.getElementById("timeDate").innerHTML = " 本站已各种夹缝中安全运行 " + dnum + " 天 ";
    document.getElementById("times").innerHTML = hnum + " 小时 " + mnum + " 分 " + snum + " 秒";
}
setInterval("createtime()", 250);
</script>

<!-- 添加网站运行时间》-->
  </div>
  
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


  

  
</footer>


  <!-- SCRIPTS -->
  
  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  <script  src="/js/local-search.js" ></script>



  
    <script  src="/js/img-lazyload.js" ></script>
  



  



  
    <script  src="https://cdn.jsdelivr.net/npm/tocbot@4/dist/tocbot.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4/anchor.min.js" ></script>
  
  
    <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js" ></script>
  



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>




  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
      typing(title)
      
    })(window, document);
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>


</body>
</html>
